package corba.master.isi.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


/**
 * This class is use to manage corba.master.isi.database creation with
 * JDBC access to Derby.
 *
 * Instructions for how to run this program are
 * given in <A HREF=example.html>example.html</A>.
 *
 * Derby applications can run against Derby running in an embedded
 * or a client/server framework. When Derby runs in an embedded framework,
 * the Derby application and Derby run in the same JVM. The application
 * starts up the Derby engine. When Derby runs in a client/server framework,
 * the application runs in a different JVM from Derby. The application only needs
 * to start the client driver, and the connectivity framework provides network connections.
 * (The server must already be running.)
 *
 * <p>When you run this application, give one of the following arguments:
 *    * embedded (default, if none specified)
 *    * derbyclient (will use the Net client driver to access Network Server)
 *    * jccjdbcclient (if Derby is running embedded in the JCC Server framework)
 *
 */
public class CreateDatabase
{
	/* the default framework is embedded*/
	public String framework = "embedded";
	public String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	public String protocol = "jdbc:derby:";

	public static void main(String[] args)
	{
		new CreateDatabase().go(args);
	}

	void go(String[] args)
	{
		/* parse the arguments to determine which framework is desired*/
		parseArguments(args);

		System.out.println("isiBankDB starting in " + framework + " mode.");

		try
		{
			/*
               The driver is installed by loading its class.
               In an embedded environment, this will start up Derby, since it is not already running.
			 */
			Class.forName(driver).newInstance();
			System.out.println("Loaded the appropriate driver.");

			Connection conn = null;
			Properties props = new Properties();
			props.put("user", "user1");
			props.put("password", "user1");

			/*
               The connection specifies create=true to cause
               the corba.master.isi.database to be created. To remove the corba.master.isi.database,
               remove the directory isiBankDB and its contents.
               The directory isiBankDB will be created under
               the directory that the system property
               derby.system.home points to, or the current
               directory if derby.system.home is not set.
			 */
			conn = DriverManager.getConnection(protocol +
					"isiBankDB;create=true", props);
			System.out.println("Connected to and created corba.master.isi.database isiBankDB");
			conn.setAutoCommit(false);

			/*
               Creating a statement lets us issue commands against
               the connection.
			 */
			Statement s = conn.createStatement();
			try{
				s.execute("drop table User_History");
				System.out.println("Dropped table User_History");
				s.execute("drop table Title_History");
				System.out.println("Dropped table Title_History");
				s.execute("drop table Alarm");
				System.out.println("Dropped table Alarm");
				s.execute("drop table Action");
				System.out.println("Dropped table Action");
				s.execute("drop table Title");
				System.out.println("Dropped table Title");
				s.execute("drop table Account");
				System.out.println("Dropped table Account");
			} catch(Exception e){
				
			}

				/*
               We create the tables
				 */
				s.execute("create table Account(account_id int, " +
						"name varchar(20), " +
						"fname varchar(20)," +
						"birthdate Date," +
						"cash float," +
						"login varchar(30)," +
						"passwd varchar(20)," +
				"constraint pk_user primary key (account_id))");
				System.out.println("Created table Count");


				s.execute("create table Title(title_id int, " +
						"label varchar(20), " +
						"introduced_date Date," +
						"cur_course float," +
						"min_course float," +
						"max_course float," +
						"total_action int," +
				"constraint pk_title primary key (title_id))");
				System.out.println("Created table Title");


				s.execute("create table Action(action_id int, " +
						"title_id int, " +
						"account_id int," +
						"total_nb int," +
						"constraint pk_action primary key (action_id)," +
						"constraint fk_title_action foreign key (title_id) references Title (title_id)," +
				"constraint fk_account_action foreign key (account_id) references Account (account_id))");
				System.out.println("Created table Action");


				s.execute("create table Alarm(alarm_id int, " +
						"title_id int, " +
						"account_id int, " +
						"type varchar(3)," +
						"limit float," +
						"constraint pk_alarm primary key (alarm_id)," +
						"constraint fk_title_alarm foreign key (title_id) references Title(title_id)," +
						"constraint fk_account_alarm foreign key (account_id) references Account(account_id)," +
				"constraint chk_type_alarm check(type in ('sup', 'inf')))");
				System.out.println("Created table Alarm");

				s.execute("create table Title_History(title_h_id int, date Date , " +
						"title_course float, " +
						"title_id int,"+
						"constraint pk_th primary key (title_h_id)," +
				"constraint fk_title_title_history foreign key (title_id) references Title (title_id))");
				System.out.println("Created table Title hystory");

				s.execute("create table User_History(user_h_id int, date Date , " +
						"operation varchar(50), " +
						"account_id int,"+
						"constraint pk_uh primary key (user_h_id)," +
				"constraint fk_account_user_history foreign key (account_id) references Account (account_id))");
				System.out.println("Created table User history");


				/* s.execute("insert into derbyDB values (1956,'Webster St.')");
            System.out.println("Inserted 1956 Webster");
            s.execute("insert into derbyDB values (1910,'Union St.')");
            System.out.println("Inserted 1910 Union");
            s.execute(
                "update derbyDB set num=180, addr='Grand Ave.' where num=1956");
            System.out.println("Updated 1956 Webster to 180 Grand");

            s.execute(
                "update derbyDB set num=300, addr='Lakeshore Ave.' where num=180");
            System.out.println("Updated 180 Grand to 300 Lakeshore");*/

				/*
               We select the rows and verify the results.
				 */
				/*  ResultSet rs = s.executeQuery(
                    "SELECT num, addr FROM derbyDB ORDER BY num");

            if (!rs.next())
            {
                throw new Exception("Wrong number of rows");
            }

            if (rs.getInt(1) != 300)
            {
                throw new Exception("Wrong row returned");
            }

            if (!rs.next())
            {
                throw new Exception("Wrong number of rows");
            }

            if (rs.getInt(1) != 1910)
            {
                throw new Exception("Wrong row returned");
            }

            if (rs.next())
            {
                throw new Exception("Wrong number of rows");
            }

            System.out.println("Verified the rows");

            rs.close();
				 */





				/*
               We release statement resources.
				 */

				s.close();
				System.out.println("Closed statement");

				/*
               We end the transaction and the connection.
				 */
				conn.commit();
				conn.close();
				System.out.println("Committed transaction and closed connection");

				/*
               In embedded mode, an application should shut down Derby.
               If the application fails to shut down Derby explicitly,
               the Derby does not perform a checkpoint when the JVM shuts down, which means
               that the next connection will be slower.
               Explicitly shutting down Derby with the URL is preferred.
               This style of shutdown will always throw an "exception".
				 */
				boolean gotSQLExc = false;

				if (framework.equals("embedded"))
				{
					try
					{
						DriverManager.getConnection("jdbc:derby:;shutdown=true");
					}
					catch (SQLException se)
					{
						gotSQLExc = true;
					}

					if (!gotSQLExc)
					{
						System.out.println("Database did not shut down normally");
					}
					else
					{
						System.out.println("Database shut down normally");
					}
				}
			}
			catch (Throwable e)
			{
				System.out.println("exception thrown:");

				if (e instanceof SQLException)
				{
					printSQLError((SQLException) e);
				}
				else
				{
					e.printStackTrace();
				}
			}

			System.out.println("CreateDatabase finished");
		}

		static void printSQLError(SQLException e)
		{
			while (e != null)
			{
				System.out.println(e.toString());
				e = e.getNextException();
			}
		}

		private void parseArguments(String[] args)
		{
			int length = args.length;

			for (int index = 0; index < length; index++)
			{
				if (args[index].equalsIgnoreCase("jccjdbcclient"))
				{

					framework = "jccjdbc";
					driver = "com.ibm.db2.jcc.DB2Driver";
					protocol = "jdbc:derby:net://localhost:1527/";
				}
				if (args[index].equalsIgnoreCase("derbyclient"))
				{
					framework = "derbyclient";
					driver = "org.apache.derby.jdbc.ClientDriver";
					protocol = "jdbc:derby://localhost:1527/";
				}
			}
		}
	}
